package com.cn.elite.nj;
import oracle.jdbc.OracleTypes;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.datasource.*;
import javax.sql.*;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.security.NoSuchAlgorithmException;
import java.security.UnrecoverableKeyException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Timestamp;
import java.util.*;

import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;

import com.elite.algorithm.GUID;

import cryptix.message.EncryptedMessage;
import cryptix.message.KeyBundleMessage;
import cryptix.message.LiteralMessage;
import cryptix.message.Message;
import cryptix.message.MessageException;
import cryptix.message.MessageFactory;
import cryptix.message.NotEncryptedToParameterException;
import cryptix.pki.KeyBundle;

public class apbean {   
	private static final Log log=LogFactory.getLog(apbean.class);
    private DataSource dataSource;
    private DataSourceTransactionManager tran;
    private String[] arrayType;
    private String[] arrayType1;
    private String[] arrayType2;
    private int num=0;
    public String temp="";
    
	public String getTemp() {
		return temp;
	}
	public void setTemp(String temp) {
		this.temp = temp;
	}
	public String[] getArrayType2() {
		return arrayType2;
	}
	public void setArrayType2(String[] arrayType2) {
		this.arrayType2 = arrayType2;
	}
	public String[] getArrayType1() {
		return arrayType1;
	}
	public void setArrayType1(String[] arrayType1) {
		this.arrayType1 = arrayType1;
	}
	public String[] getArrayType() {
		return arrayType;
	}
	public void setArrayType(String[] arrayType) {
		this.arrayType = arrayType;
	}
	public DataSource getDataSource() {
		return dataSource;
	}
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}
	public DataSourceTransactionManager getTran() {
		return tran;
	}
	public void setTran(DataSourceTransactionManager tran) {
		this.tran = tran;
	}
	
	public String gettemp(String a){
       if(a!=null){
    	   String[] aa=a.split(",");
    	   if(aa.length>3){
    		   return getChar(aa[2]);
    	   }else{
    		   return "fail"; 
    	   }
       }else{
    	   return "fail";
       }
	}
	public int getcount1(String a){
		String sql="select count(*) from custlists where custlist_xxx='"+a+"'";
		log.info("countsql:"+sql);
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		int count=jdbcTemplate.queryForInt(sql);
		return count;
	}
	public void execute1(){
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		jdbcTemplate.execute("{call dbo.insertcustomer()}");
	}
    public int[] update(String[] sql) throws Exception {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        int[] resultList = jdbcTemplate.batchUpdate(sql);
        return resultList;
  }
	public String getindexchar(){
		if(arrayType==null){
			log.info("获得indexchar失败");
			return "false";
		}else{
			if(arrayType.length<4){
				log.info("获得indexchar失败");
				return "false";
			}else{
				if(arrayType[2].equals("")){
					log.info("获得indexchar失败");
					return "false";
				}else{
					log.info("获得indexchar成功:"+arrayType[2]);
					return arrayType[2];
				}
			}
		}
	}
	
	public String getdatebaseType(){
		if(arrayType==null){
			log.info("获得datebaseType失败");
			return "false";
		}else{
			if(arrayType.length<4){
				log.info("获得datebaseType失败");
				return "false";
			}else{
				if(arrayType[0].equals("")||arrayType[0].equals("null")){
					log.info("获得datebaseType失败");
					return "false";
				}else{
					log.info("获得datebaseType成功:"+arrayType[0]);
					return arrayType[0];
				}
			}
		}
	}
	public String gettableName(){
		if(arrayType==null){
			log.info("获得tableName失败");
			return "false";
		}else{
			if(arrayType.length<4){
				log.info("获得tableName失败");
				return "false";
			}else{
				if(arrayType[1].length()<1){
					log.info("获得tableName失败");
					return "false";
				}else{
					log.info("获得tableName成功:"+arrayType[1]);
					return arrayType[1];
				}
			}
		}
	}
	
	public String gettableName1(){
		if(arrayType==null){
			log.info("获得tableName失败");
			return "false";
		}else{
			if(arrayType.length<4){
				log.info("获得tableName失败");
				return "false";
			}else{
				if(arrayType[3].length()<1){
					log.info("获得tableName失败");
					return "false";
				}else{
					log.info("获得tableName成功:"+arrayType[3]);
					return arrayType[3];
				}
			}
		}
	}
	
	public String getChar(String a){
		int c=a.indexOf("\"");
		if(c==-1){
			return a;
		}else{
			a=a.replace("\"", "");
			return a;
		}
	}
	
	public boolean judgeTable(String[] arrayType11,String[] arrayType22){
		if(arrayType11!=null&&arrayType11.length>0){
			if(arrayType22!=null&&arrayType22.length>0){
				for(int i=0;i<arrayType11.length;i++){
					log.info("字段:"+arrayType11[i]+"--"+"字段类型:"+arrayType22[i]);
				}
				return true;
			}else{
				log.info("获得字段类型失败");
				return false;
			}
		}else{
			log.info("获得字段失败");
			return false;
		}
	}
	
	public String getsql_1(String tableName,String datebaseType,String[] arrayType11){
		String sql="";
		if(datebaseType.equals("MSSQL")||datebaseType.equals("Oracle")){
			sql="insert into "+  tableName  +"(";
			for(int i=0;i<arrayType11.length;i++){
				if(i==(arrayType11.length-1)){
					sql=sql+arrayType11[i]+",createdate)";
				}else{
					sql=sql+arrayType11[i]+",";
				}
			}
			log.info("getsql_1:"+sql);
			return sql;
		}else{
			log.info("获取tableName失败:"+tableName);
			return sql;
		}
	   
	}
	
	public String getsql_2(String a,String[] arrayType22){
		String datebaseType=getdatebaseType();
		String guid=new GUID().toString();
		String sql="('"+guid+"',";
		String[] data1=a.split(",");
		if(data1!=null&&data1.length>0){
			for(int i=0;i<data1.length;i++){
				data1[i]=getChar(data1[i]);
				if(arrayType22[i+1].equals("string")){
					data1[i]="'"+data1[i]+"'";
				}else if(arrayType22[i+1].equals("int")){
					
				}else if(arrayType22[i+1].equals("date")){
					if(datebaseType.equals("MSSQL")){
						data1[i]="'"+data1[i]+"'";
					}else if(datebaseType.equals("Oracle")){
						data1[i]="to_date('"+data1[i]+"','yyyy-MM-dd HH24:mm:ss')";
					}else{
						log.info("获取datebaseType失败:"+datebaseType);
						return "false";
					}
					
				}else{
					log.info("获得非法字段类型");
					return "false";
				}
				
				if(i==(data1.length-1)){
					sql=sql+data1[i]+",sysdate)";
				}else{
					sql=sql+data1[i]+",";
				}
				
			}
			log.info("getsql_2:"+sql);
			return sql;
		}else{
			log.info("获得TXT数据失败");
			return "false";
		}
		
	}
	
	public String getfilepath(String path,String filename){
		String indexchar=getindexchar();
		path=path.substring(0, path.lastIndexOf("\\")+1);
		File file = new File(path);
		if(file==null){
			log.info("没有文件");
			return "nofile";
		}else{
			if(file.listFiles().length>0){
				for (int i = 0; i < file.listFiles().length; i++){
					log.info("文件路径：" + file.listFiles()[i].getPath());
					log.info("文件名：" + file.listFiles()[i].getName());
					if(!filename.equals(file.listFiles()[i].getName())){
						if(file.listFiles()[i].getName().indexOf(indexchar)!=-1){
							return file.listFiles()[i].getPath();
						}else{
							log.info("没有新文件");
							return "nofile";
						}
					}
					
				}
			}else{
				log.info("没有新文件");
				return "nofile";
			}
		}

		log.info("没有新文件");
		return "nofile";
		
	}
	
	public String[] getupdatesql(InputStreamReader input,String sql1,String[] arrayType22) throws IOException{
		String[] updatesql;
        BufferedReader reader = new BufferedReader(input);
        List<String> cols = new ArrayList<String>();
        int rows = 0;
        int n=0;
        for(String line = reader.readLine(); line != null; line = reader.readLine()){
        	if(n==0){
               n=1;
        	}else{
            	String sql=sql1+"  Values  "+getsql_2(line,arrayType22);
            	log.info("SQL语句:"+sql);  
    			cols.add(rows, sql);
    			rows ++;
        	}
        }
        updatesql=new String[rows];
        for(int i=0;i<cols.size();i++){
        	updatesql[i]=cols.get(i);
        }
        return updatesql;
	}
	
	public int getcount(int[] result1){
    	int count=0;
        for(int i=0;i<result1.length;i++){
        	count=count+result1[i];
        }
        return count;
	}
	public int getfile(int[] result1){
    	int count=0;
        for(int i=0;i<result1.length;i++){
        	if(result1[i]==0){
        		count=count+1;
        	}
        }
        return count;
	}
    public String run_excute() {
    	log.info("---------run_excute------------");
    	JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        String param2Value = (String) jdbcTemplate.execute(
            new CallableStatementCreator() {
                public CallableStatement createCallableStatement(Connection con){
                    String storedProc = "{call "+arrayType[3]+"(?,?)}";// 调用的sql
                    CallableStatement cs = null;
                    try{
                   	 cs = con.prepareCall(storedProc);
                   }catch(Exception e){
                   	log.info("cs = con.prepareCall(storedProc)错误:"+e);
                   }
                   try{
                   	cs.setInt(1,0);// 设置输入参数的值
                   	cs.setInt(2,0);// 设置输入参数的值
                   }catch(Exception e){
                   	log.info("设置输入参数的值错误:"+e);
                   }
                   try{
                   	cs.registerOutParameter(1, OracleTypes.INTEGER);// 注册输出参数的类型
                   	cs.registerOutParameter(2, OracleTypes.INTEGER);// 注册输出参数的类型
                   }catch(Exception e){
                   	log.info("注册输出参数的类型错误:"+e);
                   }
                    return cs;
                }
            }, new CallableStatementCallback() {
                public Object doInCallableStatement(CallableStatement cs)  {
                    try{
                    	cs.execute();
                    }catch(Exception e){
                    	log.info("cs.execute()错误:"+e);
                    }
                    try{
                    	return cs.getInt(1)+"&&"+cs.getInt(2);// 获取输出参数的值
                    }catch(Exception e){
                    	log.info("cs:"+e);
                    	return null;
                    }
            }
        });
        log.info("param2Value:"+param2Value);
        return param2Value;
    } 
	public String getsql1(String[] arrayType11,String[] arrayType22,String tableName,String datebaseType){
		String sql1="";
    	if(judgeTable(arrayType11,arrayType22)){
    		sql1=getsql_1(tableName,datebaseType,arrayType11);
    		if(sql1.equals("")||sql1.length()<1){
        		return "fail";
    		}else{
    			log.info("前置SQL语句:"+sql1);
    			return sql1;
    		}
    	}else{
    		return "fail";
    	}
	}
	   public InputStreamReader decrypt(String path1,InputStream in1) throws IOException {
		   InputStreamReader inread=null;
	       //**********************************************************************
	       // Dynamically register both the Cryptix JCE and Cryptix OpenPGP 
	       // providers. 
	       //**********************************************************************
	       java.security.Security.addProvider(new cryptix.jce.provider.CryptixCrypto());
	       java.security.Security.addProvider(new cryptix.openpgp.provider.CryptixOpenPGP());
	       //**********************************************************************
	       // First read the key. 
	       //**********************************************************************
	       KeyBundle bundle = null;
	       MessageFactory mf = null;	       
	       try {
	           FileInputStream in = new FileInputStream(path1);
	           mf = MessageFactory.getInstance("OpenPGP");
	           Collection msgs = mf.generateMessages(in);	           
	           KeyBundleMessage kbm = (KeyBundleMessage)msgs.iterator().next();	           
	           bundle = kbm.getKeyBundle();
	           in.close();	       
	       }catch (IOException ioe) {
	    	   log.info("IOException... You did remember to run the GenerateAndWriteKey example first, right?");
	    	   log.info(ioe);
	       } catch (NoSuchAlgorithmException nsae) {
	    	   log.info("Cannot find the OpenPGP MessageFactory. "+
	               "This usually means that the Cryptix OpenPGP provider is not "+
	               "installed correctly.");
	    	   log.info(nsae);
	       } catch (MessageException me) {
	    	   log.info("Reading keybundle failed.");
	    	   log.info(me);
	       }


	       //**********************************************************************
	       //  Read the message.
	       //**********************************************************************
	       EncryptedMessage em = null;
	       try {
	           Collection msgs = mf.generateMessages(in1);
	           em = (EncryptedMessage)msgs.iterator().next();
	           in1.close();	       
	       }catch (IOException ioe) {
	    	   log.info("IOException... You did remember to run the Encrypt example first, right?");
	    	   log.info(ioe);
	       } catch (MessageException me) {
	    	   log.info("Reading message failed.");
	    	   log.info(me);
	       }
	       //**********************************************************************
	       // Decrypt the message.
	       //**********************************************************************
	       try {
	           Message msg = em.decrypt(bundle, "TestingPassphrase".toCharArray());
	           inread = new InputStreamReader(((LiteralMessage)msg).getBinaryDataInputStream(),"gbk");        
	       }catch (NotEncryptedToParameterException netpe) {
	    	   log.info("Not encrypted to this key.");
	    	   log.info(netpe);
	       } catch (UnrecoverableKeyException uke) {
	    	   log.info("Invalid passphrase.");
	    	   log.info(uke);
	       }catch (MessageException me) {
	    	   log.info("Decrypting message failed.");
	    	   log.info(me);
	       }
	       return inread;
	   }

}
